﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Caching;
using System.Text;
using System.IO;
using MySQL.Db;
using Gollie.WebCache;
using Gollie.Extensions;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Gollie.DataTables
{
	public class DepositsPerVendorPerPeriodItem
	{
		public string Period { get; set; }
		public decimal Deposit { get; set; }
		public decimal Credit { get; set; }
		public decimal Used { get; set; }
	}

	public class DepositsPerVendorPerPeriod : BaseBindable<DepositsPerVendorPerPeriodItem> 
	{
		private uint vendorId;
		private static string cacheName = "depositspervendorperperiod";

		private static Dictionary<string, ICellStyle> cellStyles = new Dictionary<string, ICellStyle>();

		DateTime initialFrom;
		DateTime initialTo;

		private DepositsPerVendorPerPeriod(uint vendorId, DateTime from, DateTime to, string period)
		{
			this.vendorId = vendorId;

			this.initialFrom = from;
			this.initialTo = to;

			ResultSet resultSet = Database.Queries.GetDepositsPerVendorPerPeriod(vendorId, from, to, period);

			this.AddDetails(resultSet, from, to, period);
		}

		private void CreateCellStyles(IWorkbook workBook)
		{
			ICellStyle style = null;
			IFont titleFont = workBook.CreateFont();
			titleFont.FontName = "Trebuchet MS";
			titleFont.FontHeightInPoints = 16;
			titleFont.Boldweight = 700;
			style = workBook.CreateCellStyle();
			style.SetFont(titleFont);
			cellStyles["title"] = style;

			IFont generatedDateFont = workBook.CreateFont();
			generatedDateFont.FontName = "Trebuchet MS";
			generatedDateFont.FontHeightInPoints = 12;
			generatedDateFont.Boldweight = 700;
			style = workBook.CreateCellStyle();
			style.SetFont(generatedDateFont);
			cellStyles["generatedDate"] = style;

			IFont periodFont = workBook.CreateFont();
			periodFont.FontName = "Trebuchet MS";
			periodFont.FontHeightInPoints = 10;
			periodFont.Boldweight = 700;
			style = workBook.CreateCellStyle();
			style.SetFont(periodFont);
			cellStyles["period"] = style;

			IFont tableHeaderFont = workBook.CreateFont();
			tableHeaderFont.FontName = "Trebuchet MS";
			tableHeaderFont.FontHeightInPoints = 8;
			tableHeaderFont.Boldweight = 700;
			tableHeaderFont.Color = IndexedColors.White.Index;
			style = workBook.CreateCellStyle();
			style.SetFont(tableHeaderFont);
			style.FillForegroundColor = IndexedColors.Black.Index;
			style.FillPattern = FillPattern.SolidForeground;
			cellStyles["tableheader"] = style;

			IFont itemFont = workBook.CreateFont();
			itemFont.FontName = "Trebuchet MS";
			itemFont.FontHeightInPoints = 8;
			itemFont.Boldweight = 0;
			style = workBook.CreateCellStyle();
			style.SetFont(itemFont);
			cellStyles["item"] = style;

			IFont tableFooterFont = workBook.CreateFont();
			tableFooterFont.FontName = "Trebuchet MS";
			tableFooterFont.FontHeightInPoints = 8;
			tableFooterFont.Boldweight = 700;
			style = workBook.CreateCellStyle();
			style.SetFont(tableFooterFont);
			style.BorderTop = BorderStyle.Thick;
			style.TopBorderColor = IndexedColors.Black.Index;
			cellStyles["footer"] = style;
		}

		private void AddDetails(ResultSet resultSet, DateTime from, DateTime to, string periodIndicator)
		{
			int numRows = resultSet.Count;
			if (numRows > 0)
			{
				int i = 0;
				from = this.AddZeroPeriod(from, new Period(resultSet.GetString(i, "periodnumber")), periodIndicator);

				while (i < numRows)
				{
					from = this.AddPeriod(from, periodIndicator, resultSet, i);
					i++;
					if (i < numRows)
						from = this.AddZeroPeriod(from, new Period(resultSet.GetString(i, "periodnumber")), periodIndicator);
				}
			}

			this.AddZeroPeriod(from, new Period(this.IncreasePeriod(to, periodIndicator), periodIndicator), periodIndicator);
		}

		private DateTime AddZeroPeriod(DateTime from, Period toPeriod, string periodIndicator)
		{
			Period fromPeriod = new Period(from, periodIndicator);

			while (fromPeriod < toPeriod)
			{
				this.Add(new DepositsPerVendorPerPeriodItem()
				{
					Period = fromPeriod.ToString(),
					Credit = 0,
					Deposit = 0,
					Used = 0
				});

				from = this.IncreasePeriod(from, periodIndicator);

				fromPeriod = new Period(from, periodIndicator);
			}

			return from;
		}

		private DateTime AddPeriod(DateTime from, string periodIndicator, ResultSet resultSet, int i)
		{
			Period currentPeriod = new Period(resultSet.GetString(i, "periodnumber"));

			this.Add(new DepositsPerVendorPerPeriodItem()
			{
				Period = currentPeriod.ToString(),
				Credit = resultSet.GetDecimal(i, "creditamount"),
				Deposit = resultSet.GetDecimal(i, "depositamount"),
				Used = resultSet.GetDecimal(i, "usedamount")
			});

			return this.IncreasePeriod(from, periodIndicator);
		}

		private DateTime IncreasePeriod(DateTime period, string periodIndicator)
		{
			DateTime newPeriod;

			switch (periodIndicator)
			{
				case "w": newPeriod = period.AddDays(7); break;
				case "m": newPeriod = period.AddMonths(1); break;
				case "y": newPeriod = period.AddYears(1); break;
				default: newPeriod = period; break;
			}

			return newPeriod;
		}

		public void ExcelExport(string periodText, uint vendorId, Stream stream)
		{
			ResultSet resultSet = Database.Queries.GetVendorCurrentBalance(vendorId, initialFrom, initialTo);

			double outstanding = resultSet.GetDouble(0, "outstandingamount");
			double totalExpired = resultSet.GetDouble(0, "expiredamount");
			double periodExpired = resultSet.GetDouble(0, "expiredamountperiod");

			VendorInfo vi = VendorInfo.GetVendorInfo(vendorId);

			HSSFWorkbook workBook = new HSSFWorkbook();
			this.CreateCellStyles(workBook);

			DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
			dsi.Company = "Gollie.nl";
			workBook.DocumentSummaryInformation = dsi;
			var workSheet = workBook.CreateSheet("Gollie - " + vi.BedrijfsNaam);

			this.ExcelCreateTitle(workBook, workSheet, vi.BedrijfsNaam, periodText);

			int excelRow = 3;
			var eRow = workSheet.CreateRow(excelRow);
			var cellExpired1 = eRow.CreateCell(0, CellType.String);
			cellExpired1.CellStyle = cellStyles["period"];
			cellExpired1.SetCellValue("Totaal verlopen:");
			var cellExpired2 = eRow.CreateCell(1, CellType.Numeric);
			cellExpired2.CellStyle = cellStyles["period"];
			cellExpired2.SetCellValue(totalExpired);

			excelRow++;
			eRow = workSheet.CreateRow(excelRow);
			var cellOutstanding1 = eRow.CreateCell(0, CellType.String);
			cellOutstanding1.CellStyle = cellStyles["period"];
			cellOutstanding1.SetCellValue("Uitstaand");
			var cellOutstanding2 = eRow.CreateCell(1, CellType.Numeric);
			cellOutstanding2.CellStyle = cellStyles["period"];
			cellOutstanding2.SetCellValue(outstanding);

			excelRow += 2;
			eRow = workSheet.CreateRow(excelRow);
			var cellExpiredPeriod1 = eRow.CreateCell(0, CellType.String);
			cellExpiredPeriod1.CellStyle = cellStyles["period"];
			cellExpiredPeriod1.SetCellValue("Verlopen deze periode:");
			var cellExpiredPeriod2 = eRow.CreateCell(1, CellType.Numeric);
			cellExpiredPeriod2.CellStyle = cellStyles["period"];
			cellExpiredPeriod2.SetCellValue(periodExpired);
			
			excelRow += 2;
			this.ExcelCreateTableHeader(workBook, workSheet, excelRow);

			excelRow++;
			excelRow = this.ExcelCreateTableItems(workBook, workSheet, excelRow);

			workSheet.AutoSizeColumn(0);
			workSheet.AutoSizeColumn(1);

			workBook.Write(stream);

		}

		private void ExcelCreateTitle(IWorkbook workBook, ISheet workSheet, string companyName, string periodText)
		{
			int excelRow = 0;
			var eRow = workSheet.CreateRow(excelRow);
			var cellTitle = eRow.CreateCell(0);
			cellTitle.CellStyle = cellStyles["title"];
			cellTitle.SetCellValue("Dashboard " + companyName);

			var cellPeriod = eRow.CreateCell(2);
			cellPeriod.CellStyle = cellStyles["period"];
			string periodCellText = string.Format("Overzicht per {0}, periode: {1} t/m {2}", periodText, this.initialFrom.ToString("dd-MM-yyyy"), this.initialTo.AddDays(-1).ToString("dd-MM-yyyy"));
			cellPeriod.SetCellValue(periodCellText);

			excelRow++;
			eRow = workSheet.CreateRow(excelRow);
			var cellDate = eRow.CreateCell(0);
			cellDate.CellStyle = cellStyles["generatedDate"];
			cellDate.SetCellValue(DateTime.Now.ToString("yyyy-MMM-dd, HH:mm:ss"));
		}

		private void ExcelCreateTableHeader(IWorkbook workBook, ISheet workSheet, int startRow)
		{
			int excelRow = startRow;

			var eRow = workSheet.CreateRow(excelRow);
			var cell = eRow.CreateCell(0);
			cell.CellStyle = cellStyles["tableheader"];
			cell.SetCellValue("Periode");

			cell = eRow.CreateCell(1);
			cell.CellStyle = cellStyles["tableheader"];
			cell.SetCellValue("Ontvangen");

			cell = eRow.CreateCell(2);
			cell.CellStyle = cellStyles["tableheader"];
			cell.SetCellValue("Verstrekt");

			cell = eRow.CreateCell(3);
			cell.CellStyle = cellStyles["tableheader"];
			cell.SetCellValue("Verbruikt");
		}

		private int ExcelCreateTableItems(IWorkbook workBook, ISheet workSheet, int startRow)
		{
			int excelRow = startRow;

			foreach (DepositsPerVendorPerPeriodItem item in this)
			{
				string period = item.Period;
				double deposit = Convert.ToDouble(item.Deposit);
				double credit = Convert.ToDouble(item.Credit);
				double used = Convert.ToDouble(item.Used);

				var eRow = workSheet.CreateRow(excelRow);
				var cell1 = eRow.CreateCell(0, CellType.String);
				cell1.CellStyle = cellStyles["item"];

				var cell2 = eRow.CreateCell(1, CellType.Numeric);
				cell2.CellStyle = cellStyles["item"];

				var cell3 = eRow.CreateCell(2, CellType.Numeric);
				cell3.CellStyle = cellStyles["item"];

				var cell4 = eRow.CreateCell(3, CellType.Numeric);
				cell4.CellStyle = cellStyles["item"];

				cell1.SetCellValue(period);
				cell2.SetCellValue(deposit);
				cell3.SetCellValue(credit);
				cell4.SetCellValue(used);

				excelRow++;
			}

			string startRowStr = (startRow + 1).ToString();
			string endRowStr = excelRow.ToString();

			var footerRow = workSheet.CreateRow(excelRow);

			var footerCell1 = footerRow.CreateCell(0, CellType.String);
			footerCell1.CellStyle = cellStyles["footer"];
			footerCell1.SetCellValue("Totaal");

			var footerCell2 = footerRow.CreateCell(1, CellType.String);
			footerCell2.CellStyle = cellStyles["footer"];
			footerCell2.SetCellFormula("SUM(B" + startRowStr + ":B" + endRowStr + ")");

			var footerCell3 = footerRow.CreateCell(2, CellType.String);
			footerCell3.CellStyle = cellStyles["footer"];
			footerCell3.SetCellFormula("SUM(C" + startRowStr + ":C" + endRowStr + ")");

			var footerCell4 = footerRow.CreateCell(3, CellType.String);
			footerCell4.CellStyle = cellStyles["footer"];
			footerCell4.SetCellFormula("SUM(D" + startRowStr + ":D" + endRowStr + ")");

			return excelRow;
		}

		public static DepositsPerVendorPerPeriod GetInstance(uint vendorId, DateTime from, DateTime to, string periodIndicator)
		{
			StringBuilder str = new StringBuilder(cacheName);
			str.Append("_");
			str.Append(vendorId);
			str.Append("_");
			str.Append(from.Date);
			str.Append("_");
			str.Append(to.Date);
			str.Append("_");
			str.Append(periodIndicator);

			DepositsPerVendorPerPeriod instance = (DepositsPerVendorPerPeriod)DataCache.Cache[str.ToString()];
			if (instance == null)
			{
				instance = new DepositsPerVendorPerPeriod(vendorId, from, to, periodIndicator);
				DataCache.Insert(str.ToString(), instance, DateTime.Now.AddSeconds(300));
			}

			return instance;
		}

		private class Period
		{
			public Period(string dbString)
			{
				this.Year = Convert.ToInt32(dbString.Substring(0, 4));
				if (dbString.Length > 4)
					this.YearPeriod = Convert.ToInt32(dbString.Substring(4));
				else
					this.YearPeriod = 0;
			}

			public Period(DateTime dt, string periodIndicator)
			{
				Year = dt.Year;

				switch (periodIndicator)
				{
					case "w": WeekNumber wn = dt.WeekNumber(); Year = wn.Year; YearPeriod = wn.Week; break;
					case "m": YearPeriod = dt.Month; break;
					case "y": YearPeriod = 0; break;
				}
			}

			public static bool operator <(Period p1, Period p2)
			{
				return (p1.CompareTo(p2) < 0);
			}

			public static bool operator >(Period p1, Period p2)
			{
				return (p1.CompareTo(p2) > 0);
			}

			public static bool operator <=(Period p1, Period p2)
			{
				return (p1.CompareTo(p2) <= 0);
			}

			public static bool operator >=(Period p1, Period p2)
			{
				return (p1.CompareTo(p2) >= 0);
			}

			public static bool operator ==(Period p1, Period p2)
			{
				return (p1.CompareTo(p2) == 0);
			}

			public static bool operator !=(Period p1, Period p2)
			{
				return (p1.CompareTo(p2) != 0);
			}

			public override bool Equals(object obj)
			{
				return this == (Period)obj;
			}

			public override int GetHashCode()
			{
				return Year * 100 + YearPeriod;
			}

			public int Year { get; set; }
			public int YearPeriod { get; set; }

			public int CompareTo(Period p)
			{
				return (this.Year > p.Year ? 1 : this.Year < p.Year ? -1 : this.YearPeriod > p.YearPeriod ? 1 : this.YearPeriod < p.YearPeriod ? -1 : 0);
			}

			public override string ToString()
			{
				if (this.YearPeriod != 0)
					return this.Year.ToString() + "-" + this.YearPeriod.ToString("00");

				return this.Year.ToString();
			}
		}
	}
}